Imports ADOConnection
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient

Imports System.Text

Public Class CSLNX
    Private m_SLNX_ID As Integer
    Private m_DPNX_ID As Integer
    Private m_MATHANG_ID As Integer
    Private m_SLKH As Double
    Private m_SLTH As Double
    Private m_THANHTIEN As Double
    Private m_GHICHU As String

#Region "Property"
    Public Property SLNX_ID() As Integer
        Get
            Return m_SLNX_ID
        End Get
        Set(ByVal value As Integer)
            m_SLNX_ID = value
        End Set
    End Property

    Public Property DPNX_ID() As Integer
        Get
            Return m_DPNX_ID
        End Get
        Set(ByVal value As Integer)
            m_DPNX_ID = value
        End Set
    End Property

    Public Property MATHANG_ID() As Integer
        Get
            Return m_MATHANG_ID
        End Get
        Set(ByVal value As Integer)
            m_MATHANG_ID = value
        End Set
    End Property

    Public Property SLKH() As Double
        Get
            Return m_SLKH
        End Get
        Set(ByVal value As Double)
            m_SLKH = value
        End Set
    End Property

    Public Property SLTH() As Double
        Get
            Return m_SLTH
        End Get
        Set(ByVal value As Double)
            m_SLTH = value
        End Set
    End Property

    Public Property THANHTIEN() As Double
        Get
            Return m_THANHTIEN
        End Get
        Set(ByVal value As Double)
            m_THANHTIEN = value
        End Set
    End Property

    Public Property GhiChu() As String
        Get
            Return m_GhiChu
        End Get
        Set(ByVal value As String)
            m_GhiChu = value
        End Set
    End Property
#End Region

    Public Function Insert() As Integer
        Dim objConnection As IADOConnection = g_objConnFactory.GetConnection
        Dim objDBUtility As IDBUtility = objConnection.DBUtility

        Dim objStringBuilderSLNX As New StringBuilder(250)
        With objStringBuilderSLNX
            .Append("INSERT INTO tblSLNX " & _
                    "(dpnx_id, mathang_id, slkh, slth, thanhtien, ghichu) VALUES(")
            .Append(objDBUtility.GetParamPlaceHolder("dpnx_id", True))
            .Append(objDBUtility.GetParamPlaceHolder("mathang_id", True))
            .Append(objDBUtility.GetParamPlaceHolder("slkh", True))
            .Append(objDBUtility.GetParamPlaceHolder("slth", True))
            .Append(objDBUtility.GetParamPlaceHolder("thanhtien", True))
            .Append(objDBUtility.GetParamPlaceHolder("ghichu", False))
            .Append(")")
        End With

        Dim sInsert As String = objStringBuilderSLNX.ToString
        Dim objCommand As IDbCommand = objConnection.CreateCommand(sInsert)
        Dim objTransaction As IDbTransaction = objConnection.BeginTransaction
      
        Try
            With objCommand
                .Transaction = objTransaction
                .Parameters.Add(objDBUtility.CreateParameter("dpnx_id", DbType.Decimal, m_DPNX_ID, 0))
                .Parameters.Add(objDBUtility.CreateParameter("mathang_id", DbType.Decimal, m_MATHANG_ID, 0))
                .Parameters.Add(objDBUtility.CreateParameter("slkh", DbType.Decimal, m_SLKH, 0))
                .Parameters.Add(objDBUtility.CreateParameter("slth", DbType.Decimal, m_SLTH, 0))
                .Parameters.Add(objDBUtility.CreateParameter("thanhtien", DbType.Decimal, m_THANHTIEN, 0))

                If m_GHICHU Is Nothing Then
                    .Parameters.Add(objDBUtility.CreateParameter("ghichu", DbType.String, DBNull.Value, 0))
                Else
                    .Parameters.Add(objDBUtility.CreateParameter("ghichu", DbType.String, m_GHICHU, m_GHICHU.Length))
                End If
            End With
            m_SLNX_ID = CInt(objDBUtility.InsertAndReturnSerialKey(objCommand, "slnx_id", "tblSLNX"))
            objTransaction.Commit()
        Catch ex As System.Exception
            objTransaction.Rollback()
            Throw ex
        Finally
            objConnection.Close()
        End Try
        Return m_SLNX_ID
    End Function


    Public Function Update() As Long
        Dim lRowsAffected As Long = 0
        Dim objConnection As IADOConnection = g_objConnFactory.GetConnection
        Dim objDBUtility As IDBUtility = objConnection.DBUtility

        Dim strSQL As String = "UPDATE tblSLNX SET"
        strSQL &= " DPNX_ID = " & objDBUtility.GetParamPlaceHolder("DPNX_ID", True)
        strSQL &= " MATHANG_ID = " & objDBUtility.GetParamPlaceHolder("MATHANG_ID", True)
        strSQL &= " SLKH = " & objDBUtility.GetParamPlaceHolder("SLKH", True)
        strSQL &= " SLTH = " & objDBUtility.GetParamPlaceHolder("SLTH", True)
        strSQL &= " THANHTIEN = " & objDBUtility.GetParamPlaceHolder("THANHTIEN", True)
        strSQL &= " GHICHU = " & objDBUtility.GetParamPlaceHolder("GHICHU", False)
        strSQL &= " WHERE SLNX_ID = " & objDBUtility.GetParamPlaceHolder("SLNX_ID", False)

        Dim objTransaction As IDbTransaction = objConnection.BeginTransaction
        Dim objCommand As IDbCommand = objConnection.CreateCommand(strSQL)

        Try
            With objCommand
                .Transaction = objTransaction
                .Parameters.Add(objDBUtility.CreateParameter("dpnx_id", DbType.Decimal, m_DPNX_ID, 0))
                .Parameters.Add(objDBUtility.CreateParameter("mathang_id", DbType.Decimal, m_MATHANG_ID, 0))
                .Parameters.Add(objDBUtility.CreateParameter("slkh", DbType.Decimal, m_SLKH, 0))
                .Parameters.Add(objDBUtility.CreateParameter("slth", DbType.Decimal, m_SLTH, 0))
                .Parameters.Add(objDBUtility.CreateParameter("thanhtien", DbType.Decimal, m_THANHTIEN, 0))

                If m_GHICHU Is Nothing Then
                    .Parameters.Add(objDBUtility.CreateParameter("ghichu", DbType.String, DBNull.Value, 0))
                Else
                    .Parameters.Add(objDBUtility.CreateParameter("ghichu", DbType.String, m_GHICHU, m_GHICHU.Length))
                End If
                .Parameters.Add(objDBUtility.CreateParameter("slnx_id", DbType.Decimal, m_SLNX_ID, 0))
                lRowsAffected = .ExecuteNonQuery()
                objTransaction.Commit()
            End With
        Catch ex As System.Exception
            objTransaction.Rollback()
            Throw ex
        Finally
            objConnection.Close()
        End Try


        Return lRowsAffected
    End Function

    Public Function Delete(ByVal pSlnx_id As Integer) As Long
        Dim lRowsAffected As Long = 0
        Dim objConnection As IADOConnection = g_objConnFactory.GetConnection
        Dim objDBUtility As IDBUtility = objConnection.DBUtility

        Dim strSQL As String = "delete from tblSLNX where "
        strSQL &= " WHERE SLNX_ID = " & objDBUtility.GetParamPlaceHolder("SLNX_ID", False)

        Dim objTransaction As IDbTransaction = objConnection.BeginTransaction
        Dim objCommand As IDbCommand = objConnection.CreateCommand(strSQL)

        Try
            With objCommand
                .Transaction = objTransaction
                .Parameters.Add(objDBUtility.CreateParameter("slnx_id", DbType.Decimal, pSlnx_id, 0))
                lRowsAffected = .ExecuteNonQuery()
                objTransaction.Commit()
            End With
        Catch ex As System.Exception
            objTransaction.Rollback()
            Throw ex
        Finally
            objConnection.Close()
        End Try
        Return lRowsAffected
    End Function
End Class

Public Class CSLNXs
    Public Shared Function DeleteSLNXByDPNX_ID(ByVal DPNX_ID As Integer) As Long
        Dim lRowsAffected As Long = 0
        Dim objConnection As IADOConnection = g_objConnFactory.GetConnection
        Dim objDBUtility As IDBUtility = objConnection.DBUtility

        Dim strSQL As String = "delete from tblSLNX"
        strSQL &= " WHERE DPNX_ID = " & objDBUtility.GetParamPlaceHolder("DPNX_ID", False)

        Dim objTransaction As IDbTransaction = objConnection.BeginTransaction
        Dim objCommand As IDbCommand = objConnection.CreateCommand(strSQL)

        Try
            With objCommand
                .Transaction = objTransaction
                .Parameters.Add(objDBUtility.CreateParameter("dpnx_id", DbType.Decimal, DPNX_ID, 0))
                lRowsAffected = .ExecuteNonQuery()
                objTransaction.Commit()
            End With
        Catch ex As System.Exception
            objTransaction.Rollback()
            Throw ex
        Finally
            objConnection.Close()
        End Try
        Return lRowsAffected
    End Function

    Public Shared Function GetSLNXByMa(ByVal SLNX_ID As Integer) As CSLNX
        Dim obj_SLNX As New CSLNX
        Dim strSQL As String

        strSQL = "SELECT SLNX_ID, DPNX_ID, MATHANG_ID, SLKH, SLTT, THANHTIEN, GhiChu " & _
                " FROM tblSLNX" & _
                " WHERE SLNX_ID = '" & CStr(SLNX_ID) & "'"

        Dim objConnection As IADOConnection = g_objConnFactory.GetConnection

        Dim objCommand As IDbCommand = objConnection.CreateCommand(strSQL)
        Dim objReader As CDataReader = Nothing

        Try
            objReader = objConnection.GetDataReader(objCommand, False)
            While objReader.Read
                With obj_SLNX
                    .SLNX_ID = objReader.GetDecimal(0)
                    .DPNX_ID = objReader.GetDecimal(1)
                    .MATHANG_ID = objReader.GetDecimal(2)
                    .SLKH = objReader.GetDecimal(3)
                    .SLTH = objReader.GetDecimal(4)
                    .THANHTIEN = objReader.GetDecimal(5)
                    .GhiChu = objReader.GetString(6)
                End With
            End While
        Catch ex As System.Exception
            Throw ex
        Finally
            If Not IsNothing(objReader) Then
                objReader.Close()
            End If
            objConnection.Close()
        End Try

        Return obj_SLNX
    End Function

    'Ham nay dung de create DataSet
    ' pDPNX_ID: la bien Optional dung de loc cac SLNX khi can, co cac value nhu sau
    '   * pDPNX_ID = -1: lay toan bo data trong tblSLNX( dung trong frmViewPhieuNX)
    '   * pDPNX_ID = 0:  chi lay cac column cua tblSLNX ko can lay data( dung trong frmPhieuNX)
    '   * pDPNX_ID > 0:  lay data theo gia tri pDpnx_id > 0( dung trong frmPhieuNX)

    Public Shared Sub CreateDataSetSLNX(ByRef sdaMatHang As IDbDataAdapter, ByRef dsDataSet As DataSet, Optional ByVal pDPNX_ID As Integer = -1)
        Try
            Dim objConnection As IADOConnection = g_objConnFactory.GetConnection
            Dim objDBUtility As IDBUtility = objConnection.DBUtility

            Dim strSQL As String
            If pDPNX_ID > -1 Then
               strSQL = "select * from viewHT_SLNX"
                strSQL &= " where DPNX_ID in (select tblDPNX.DPNX_ID from tblDPNX where DPNX_ID = " & pDPNX_ID.ToString() & ") order by slnx_id asc"
            Else
                strSQL = "select * from viewHT_SLNX"
            End If

            Dim objCommand As IDbCommand = objConnection.CreateCommand(strSQL)
            sdaMatHang.SelectCommand = objCommand

            Dim updateSQL As String = "UPDATE tblSLNX SET SLKH = "
            updateSQL &= objDBUtility.GetParamPlaceHolder("SLKH", True)
            updateSQL &= " SLTH = " & objDBUtility.GetParamPlaceHolder("SLTH", True)
            updateSQL &= " THANHTIEN = " & objDBUtility.GetParamPlaceHolder("THANHTIEN", True)
            updateSQL &= " GHICHU = " & objDBUtility.GetParamPlaceHolder("GHICHU", False)
            updateSQL &= " WHERE DPNX_ID = "
            updateSQL &= objDBUtility.GetParamPlaceHolder("DPNX_ID", False)
            updateSQL &= " AND MATHANG_ID = "
            updateSQL &= objDBUtility.GetParamPlaceHolder("MATHANG_ID", False)

            Dim updateCommand As IDbCommand = objConnection.CreateCommand(updateSQL)
            With updateCommand
                .CommandType = CommandType.Text
                .Parameters.Add(objDBUtility.CreateParameter("SLKH", DbType.Decimal, 0, 0))
                .Parameters.Add(objDBUtility.CreateParameter("SLTH", DbType.Decimal, 0, 0))
                .Parameters.Add(objDBUtility.CreateParameter("THANHTIEN", DbType.Decimal, 0, 0))
                .Parameters.Add(objDBUtility.CreateParameter("GHICHU", DbType.String, 0, 0))
                .Parameters.Add(objDBUtility.CreateParameter("DPNX_ID", DbType.Decimal, 0, 0))
                .Parameters.Add(objDBUtility.CreateParameter("MATHANG_ID", DbType.Decimal, 0, 0))
            End With

            sdaMatHang.UpdateCommand = updateCommand

            Dim deleteSQL As String = "DELETE FROM tblSLNX WHERE DPNX_ID = "
            deleteSQL &= objDBUtility.GetParamPlaceHolder("DPNX_ID", False)
            deleteSQL &= " AND SLNX_ID = "
            deleteSQL &= objDBUtility.GetParamPlaceHolder("SLNX_ID", False)
            Dim deleteCommand As IDbCommand = objConnection.CreateCommand(deleteSQL)

            With deleteCommand
                .CommandType = CommandType.Text
                .Parameters.Add(objDBUtility.CreateParameter("DPNX_ID", DbType.Decimal, 0, 0))
                .Parameters.Add(objDBUtility.CreateParameter("SLNX_ID", DbType.Decimal, 0, 0))
            End With
            sdaMatHang.DeleteCommand = deleteCommand

            Dim insertSQL As String = "INSERT INTO tblSLNX"
            insertSQL &= "(DPNX_ID, MATHANG_ID, SLKH, SLTH, THANHTIEN, GHICHU)"
            insertSQL &= " VALUES ("
            insertSQL &= objDBUtility.GetParamPlaceHolder("DPNX_ID", True)
            insertSQL &= objDBUtility.GetParamPlaceHolder("MATHANG_ID", True)
            insertSQL &= objDBUtility.GetParamPlaceHolder("SLKH", True)
            insertSQL &= objDBUtility.GetParamPlaceHolder("SLTH", True)
            insertSQL &= objDBUtility.GetParamPlaceHolder("THANHTIEN", True)
            insertSQL &= objDBUtility.GetParamPlaceHolder("GHICHU", False)
            insertSQL &= ")"
            Dim insertCommand As IDbCommand = objConnection.CreateCommand(insertSQL)
            With insertCommand
                .CommandType = CommandType.Text
                .Parameters.Add(objDBUtility.CreateParameter("DPNX_ID", DbType.Decimal, 0, 0))
                .Parameters.Add(objDBUtility.CreateParameter("MATHANG_ID", DbType.Decimal, 0, 0))
                .Parameters.Add(objDBUtility.CreateParameter("SLKH", DbType.Decimal, 0, 0))
                .Parameters.Add(objDBUtility.CreateParameter("SLTH", DbType.Decimal, 0, 0))
                .Parameters.Add(objDBUtility.CreateParameter("THANHTIEN", DbType.Decimal, 0, 0))
                .Parameters.Add(objDBUtility.CreateParameter("GHICHU", DbType.String, 0, 0))
            End With
            sdaMatHang.InsertCommand = insertCommand
            Try
                sdaMatHang.Fill(dsDataSet)
            Catch ex As Exception
                dsDataSet = New DataSet
                sdaMatHang.Fill(dsDataSet)
            End Try
        Catch exp As Exception
            Throw exp
        End Try
    End Sub
End Class
